﻿CREATE TRIGGER tr_ContractCondition_APRByModelAge
ON t_ContractCondition_APRByModelAge
AFTER INSERT,UPDATE
AS

IF UPDATE(ContractConditionCode) AND
	 EXISTS(SELECT * FROM inserted WHERE ContractConditionCode <= '  ')BEGIN
	ROLLBACK TRAN
	RAISERROR('Contract Condition Code must be specified.',16,1)
	RETURN
END

DECLARE @Res int
DECLARE @T TABLE(ID int IDENTITY, AgeFrom int,AgeTo int,APR int)
INSERT INTO @T
SELECT APR.ModelAgeFrom,APR.ModelAgeTo,APR.APR FROM t_ContractCondition_APRByModelAge APR
INNER JOIN inserted i ON i.ContractConditionCode = APR.ContractConditionCode
ORDER BY ModelAgeFrom;

WITH Ages AS(
SELECT TOP 1 ID,AgeTo Age,APR FROM @T
UNION ALL
SELECT APR.ID,AgeTo, APR.APR --CASE WHEN APR.AgeFrom <= Ages.Age THEN 1 ELSE 0 END
FROM @T APR INNER JOIN Ages ON APR.ID = Ages.ID+1 AND APR.AgeFrom = Ages.Age+1 AND APR.APR > Ages.APR
--SELECT APR.ID,AgeTo, CASE WHEN APR.AgeFrom <= Ages.Age THEN 1 ELSE 0 END
--FROM @T APR INNER JOIN Ages ON APR.ID > Ages.ID
)
SELECT @Res = COUNT(*) FROM Ages

IF @Res = (SELECT COUNT(*) FROM @T) RETURN

ROLLBACK TRAN
RAISERROR('Wrong sequence.',16,1)
